package com.usefullc.common.util;

import com.usefullc.common.exception.BizException;
import org.apache.commons.io.FileUtils;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.cglib.beans.BeanMap;
import org.springframework.http.MediaType;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author: Sampson
 * @date: 2020-12-30 19:03
 */
public class XlsUtils {

    Logger logger = LoggerFactory.getLogger(XlsUtils.class);

    private static Sheet createWorkbookSheet(InputStream inputStream) {
        try {
            return new XSSFWorkbook(inputStream).getSheetAt(0);
        } catch (Exception e) {
            try {
                return new HSSFWorkbook(inputStream).getSheetAt(0);
            } catch (IOException ex) {
                throw new BizException(ex);
            }
        }
    }

    public static <T> List<T> read(InputStream stream, List<String> propertyList, Class<T> clazz, int readStartRow) {
        try {
            List<T> result = new ArrayList<>();
            Sheet sheet = createWorkbookSheet(stream);
            FormulaEvaluator evaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();
            int rowNum = sheet.getPhysicalNumberOfRows();
            Row row = sheet.getRow(0);
            for (int i = readStartRow; i < rowNum; i++) {
                row = sheet.getRow(i);
                if (row != null) {
                    Map<String, Object> excelMap = new HashMap<>();
                    int j = 0;
                    for (Cell cell : row) {
                        if (cell != null) {
                            Object value = null;
                            if (cell.getCellType() == CellType.STRING) {
                                value = cell.getStringCellValue();
                            } else if (cell.getCellType() == CellType.NUMERIC) {
                                value = cell.getNumericCellValue();
                            }
                            if (cell.getCellType() == CellType.BOOLEAN) {
                                value = cell.getBooleanCellValue();
                            } else if (cell.getCellType() == CellType.FORMULA) {
                                value = evaluator.evaluate(cell).getNumberValue();
                            }
                            if (value != null && !value.equals("")) {
                                excelMap.put(propertyList.get(j), value);
                                j++;
                            }
                        }
                    }
                    T t = clazz.newInstance();
                    BeanMap beanMap = BeanMap.create(t);
                    beanMap.putAll(excelMap);
                    result.add(t);
                }
            }
            return result;
        } catch (Exception e) {
            throw new BizException(e);
        }

    }


    public static void export(List<List<Object>> dataList, String title, InputStream templateInputStream, HttpServletResponse response) {
        try {
            //创建工作薄对象
            HSSFWorkbook workbook = new HSSFWorkbook(templateInputStream);//这里也可以设置sheet的Name
            //创建工作表对象
            HSSFSheet sheet = workbook.getSheetAt(0);
            //创建工作表的行
            workbook.setSheetName(0, title);

            HSSFCellStyle wrapStyle = workbook.createCellStyle();
            wrapStyle.setWrapText(true);

            HSSFCellStyle standarCellStyle = workbook.createCellStyle();

            for (int i = 0; i < dataList.size(); i++) {
                HSSFRow row = null;
                if (i == 2) {
                    row = sheet.getRow(i);
                    HSSFCell cell = row.getCell(0);
                    standarCellStyle = cell.getCellStyle();
                }
                if (i <= 2) {
                    row = sheet.getRow(i);

                } else {
                    row = sheet.createRow(i);
                }

//                HSSFRow row = sheet.getRow(i);
                List<Object> objects = dataList.get(i);
                for (int index = 0; index < objects.size(); index++) {
                    Object object = objects.get(index);

//                    HSSFCell cell = null;
//                    if (i <= 2) {
//                        cell = row.getCell(index);
//                    } else {
//                    }
                    HSSFCell cell = row.createCell(index);
                    cell.setCellStyle(standarCellStyle);
//                    if (cell != null) {
                    if (object instanceof List) {
                        List<String> listItems = new ArrayList<>();
                        List objectList = (List) object;
                        for (Object o : objectList) {
                            listItems.add(ObjectUtils.defaultIfNull(o, "").toString());
                        }
                        numberedListInCell(workbook, listItems, cell, 1, 1, wrapStyle);
                    } else {
                        cell.setCellValue(ObjectUtils.defaultIfNull(object, "").toString());
                    }
//                    }
                }
            }
            response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(title + ".xls", "utf-8"));
            response.flushBuffer();
            workbook.write(response.getOutputStream());


        } catch (Exception e) {
            throw new BizException(e);
        } finally {
//            IOUtils.closeQuietly(out);
        }

    }

    /**
     * Inserts a numbered list into a single cell.
     *
     * @param workbook      A reference to the HSSFWorkbook that 'contains' the
     *                      cell.
     * @param listItems     An ArrayList whose elements encapsulate the text for
     *                      the lists items.
     * @param cell          An instance of the HSSFCell class that encapsulates a
     *                      reference to the spreadsheet cell into which the list
     *                      will be written.
     * @param startingValue A primitive int containing the number for the first
     *                      item in the list.
     * @param increment     A primitive int containing the value that should be used
     *                      to calculate subsequent item numbers.
     */
    public static void numberedListInCell(HSSFWorkbook workbook,
                                          List<String> listItems,
                                          HSSFCell cell,
                                          int startingValue,
                                          int increment,
                                          HSSFCellStyle wrapStyle
    ) {
        StringBuilder buffer = new StringBuilder();
        int itemNumber = startingValue;
        // Note that again, an HSSFCellStye object is required and that
        // it's wrap text property should be set to 'true'
        // Note that the basic method is identical to the listInCell() method
        // with one difference; a number prefixed to the items text.
        boolean addNumber = listItems.size() > 1;

        for (String listItem : listItems) {
            if (addNumber) {
                buffer.append(itemNumber).append(". ");
            }
            buffer.append(listItem);
            buffer.append("\n");
            itemNumber += increment;
        }
        // The StringBuilder's contents are the source for the contents
        // of the cell.
        cell.setCellValue(new HSSFRichTextString(buffer.toString().trim()));
        cell.setCellStyle(wrapStyle);
    }
}
